package com.sxfq.loansupermarket.backsystem.modular.report.service.impl;

import com.sxfq.loansupermarket.backsystem.core.shiro.ShiroKit;
import com.sxfq.loansupermarket.backsystem.modular.report.entity.SearchWholeHospitalInfo;
import com.sxfq.loansupermarket.backsystem.modular.report.service.IGetTotleService;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Service
@Transactional
public class GetTotleServiceImpl implements IGetTotleService {

    public String getsqlDepartment(SearchWholeHospitalInfo searchInfo, String classification, String condition, int i, List<SearchWholeHospitalInfo> list1) {
        String sql = "";
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName1 = tableHead + "deal_info";
        String tableName2 = tableHead + "customer_info";
        sql = "   select '" + classification + "' as rirstClassification,\n" +
                "(select count(1) from (select * from  ( select c.reservations_id from " + tableName2 + " b  left join \n" +
                "" + tableName1 + " c on c.customer_id = b.id left join pay_service_info d\n" +
                "on c.pay_service_id=d.id where b.customer_status='新客首次' and\n" +
                " b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "'\n" +
                "  and "+condition+"  and c.deal_status!='作废' and c.deal_status!='已开单' group by c.reservations_id) t) t) AS outpatientVolume,\n" +
                "(select count(1) from (select * from  ( select c.reservations_id from " + tableName2 + " b  left join \n" +
                "" + tableName1 + " c on c.customer_id = b.id left join pay_service_info d\n" +
                "on c.pay_service_id=d.id where b.customer_status='新客首次' and c.deal_status='已成交' and\n" +
                " b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "'\n" +
                " and "+condition+" and c.deal_status='已成交'  group by c.reservations_id) t) t)  AS volume,\n" +
                "(  select sum(c.service_actual_pay) from " + tableName2 + " b  left join  " + tableName1 + " c on c.customer_id = b.id left join pay_service_info d on c.pay_service_id=d.id\n" +
                " where  b.customer_status='新客首次' and c.get_time>='" + searchInfo.getStartTime() + "' \n" +
                " and c.get_time<='" + searchInfo.getEndTime() + "'\n" +
                "and "+condition+" and c.deal_status='已成交' \n" +
                " ) AS departmentPerformance  from dual \n" +
                " union all\n" +
                "select '" + classification + "' as rirstClassification,\n" +
                "(select count(1) from (select * from  ( select c.reservations_id from " + tableName2 + " b left join \n" +
                "" + tableName1 + " c on c.customer_id = b.id left join pay_service_info d\n" +
                "on c.pay_service_id=d.id where b.customer_status='新客二次' and\n" +
                "b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "'\n" +
                " and "+condition+"  and c.deal_status!='作废' and c.deal_status!='已开单'  group by c.reservations_id) t) t) AS outpatientVolume,\n" +
                "(select count(1) from (select * from  ( select c.reservations_id from " + tableName2 + " b left join \n" +
                "" + tableName1 + " c on c.customer_id = b.id left join pay_service_info d\n" +
                "on c.pay_service_id=d.id where b.customer_status='新客二次' and c.deal_status='已成交' and\n" +
                "b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "'\n" +
                " and "+condition+"  group by c.reservations_id) t) t)  AS volume,\n" +
                "(  \n" +
                " select sum(c.service_actual_pay) from " + tableName2 + " b  left join  " + tableName1 + " c on c.customer_id = b.id left join pay_service_info d on c.pay_service_id=d.id\n" +
                " where  b.customer_status='新客二次' and c.get_time>='" + searchInfo.getStartTime() + "' \n" +
                " and c.get_time<='" + searchInfo.getEndTime() + "'\n" +
                "and "+condition+" and c.deal_status='已成交' \n" +
                " ) AS departmentPerformance  from dual \n" +
                " union all\n" +
                "select '" + classification + "' as rirstClassification,\n" +
                "(select count(1) from (\n" +
                "select * from  ( \n" +
                "select c.reservations_id,b.customer_status from " + tableName2 + " b  left join \n" +
                "" + tableName1 + " c on c.customer_id = b.id left join pay_service_info d\n" +
                "on c.pay_service_id=d.id where \n" +
                "b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "'\n" +
                " and "+condition+"  and c.deal_status!='作废' and c.deal_status!='已开单' ) t where t.customer_status like '%新客%' group by t.reservations_id) t) AS outpatientVolume,\n" +
                " \n" +
                "(select count(1) from (select * from  ( select c.reservations_id,b.customer_status from " + tableName2 + " b  left join \n" +
                "" + tableName1 + " c on c.customer_id = b.id left join pay_service_info d\n" +
                "on c.pay_service_id=d.id where  c.deal_status='已成交' and\n" +
                "b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "'\n" +
                " and "+condition+"  ) t \n" +
                " where t.customer_status like '%新客%'  group by t.reservations_id ) t)  AS volume,\n" +
                "(  \n" +
                " select sum(c.service_actual_pay) from " + tableName2 + " b  left join  " + tableName1 + " c on c.customer_id = b.id left join pay_service_info d on c.pay_service_id=d.id\n" +
                " where  b.customer_status like '%新客%' and c.get_time>='" + searchInfo.getStartTime() + "' \n" +
                " and c.get_time<='" + searchInfo.getEndTime() + "'\n" +
                "and "+condition+" and c.deal_status='已成交' \n" +
                " ) AS departmentPerformance  from dual \n" +
                " union all\n" +
                "select '" + classification + "' as rirstClassification,\n" +
                "(select count(1) from (select * from  ( select c.reservations_id from " + tableName2 + " b  left join \n" +
                "" + tableName1 + " c on c.customer_id = b.id left join pay_service_info d\n" +
                "on c.pay_service_id=d.id where b.customer_status='老客' and\n" +
                "b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "'\n" +
                " and "+condition+"  and c.deal_status!='作废' and c.deal_status!='已开单'  group by c.reservations_id) t) t) AS outpatientVolume,\n" +
                "(select count(1) from (select * from  ( select c.reservations_id from " + tableName2 + " b  left join \n" +
                "" + tableName1 + " c on c.customer_id = b.id left join pay_service_info d\n" +
                "on c.pay_service_id=d.id where b.customer_status='老客' and c.deal_status='已成交' and\n" +
                "b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "'\n" +
                " and "+condition+"   group by c.reservations_id) t) t)  AS volume,\n" +
                "( \n" +
                " select sum(c.service_actual_pay) from " + tableName2 + " b  left join  " + tableName1 + " c on c.customer_id = b.id left join pay_service_info d on c.pay_service_id=d.id\n" +
                " where  b.customer_status='老客' and c.get_time>='" + searchInfo.getStartTime() + "' \n" +
                " and c.get_time<='" + searchInfo.getEndTime() + "'\n" +
                "and "+condition+" and c.deal_status='已成交' \n" +
                " ) AS departmentPerformance  from dual \n" +
                " union all\n" +
                "select '" + classification + "' as rirstClassification,\n" +
                "(select count(1) from (\n" +
                "select * from  ( \n" +
                "select c.reservations_id,b.customer_status from " + tableName2 + " b  left join \n" +
                "" + tableName1 + " c on c.customer_id = b.id left join pay_service_info d\n" +
                "on c.pay_service_id=d.id where \n" +
                "b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "'\n" +
                " and "+condition+"  and c.deal_status!='作废' and c.deal_status!='已开单' ) t " +
                " where t.customer_status!='复诊' and t.customer_status!='预约' group by t.reservations_id) t) AS outpatientVolume, \n" +
                "(select count(1) from (select * from  ( select c.reservations_id,b.customer_status from " + tableName2 + " b  left join \n" +
                "" + tableName1 + " c on c.customer_id = b.id left join pay_service_info d\n" +
                "on c.pay_service_id=d.id where  c.deal_status='已成交' and\n" +
                "b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "'\n" +
                " and "+condition+"  ) t \n" +
                " where t.customer_status!='复诊' and t.customer_status!='预约'  group by t.reservations_id) t)  AS volume,\n" +
                "( \n" +
                " select sum(c.service_actual_pay) from " + tableName2 + " b  left join  " + tableName1 + " c on c.customer_id = b.id left join pay_service_info d on c.pay_service_id=d.id\n" +
                " where  b.customer_status!='复诊' and b.customer_status!='预约' and c.get_time>='" + searchInfo.getStartTime() + "' \n" +
                " and c.get_time<='" + searchInfo.getEndTime() + "'\n" +
                "and "+condition+" and c.deal_status='已成交' \n" +
                " ) AS departmentPerformance  from dual   ";
        if (i == list1.size() - 1) {
        } else {
            sql += " union all ";
        }
        return sql;
    }


    public String getTotleSqlDepartment(SearchWholeHospitalInfo searchInfo) {
        String sql = "";
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName1 = tableHead + "deal_info";
        String tableName2 = tableHead + "customer_info";
        sql = " union all   select '合计' as rirstClassification,\n" +
                "(select count(1) from (select * from  ( select a.reservations_id from " + tableName2 + " b  left join \n" +
                "" + tableName1 + " a on a.customer_id = b.id left join pay_service_info c\n" +
                "on a.pay_service_id=c.id where b.customer_status='新客首次' and\n" +
                " b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "'\n" +
                " group by a.reservations_id) t) t) AS outpatientVolume,\n" +
                "(select count(1) from (select * from  ( select a.reservations_id from " + tableName2 + " b  left join \n" +
                "" + tableName1 + " a on a.customer_id = b.id left join pay_service_info c\n" +
                "on a.pay_service_id=c.id where b.customer_status='新客首次' and a.deal_status='已成交' and\n" +
                " b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "'\n" +
                " group by a.reservations_id) t) t)  AS volume,\n" +
                "(select sum(c.service_actual_pay) from " + tableName2 + " b  left join  " + tableName1 + " c on c.customer_id = b.id left join pay_service_info d on c.pay_service_id=d.id\n" +
                " where  b.customer_status='新客首次' and c.get_time>='" + searchInfo.getStartTime() + "' \n" +
                " and c.get_time<='" + searchInfo.getEndTime() + "'\n" +
                "  and c.deal_status='已成交' \n" +
                " ) AS departmentPerformance  from dual \n" +
                " union all\n" +
                "select '合计' as rirstClassification,\n" +
                "(select count(1) from (select * from  ( select a.reservations_id from " + tableName2 + " b  left join \n" +
                "" + tableName1 + " a on a.customer_id = b.id left join pay_service_info c\n" +
                "on a.pay_service_id=c.id where b.customer_status='新客二次' and\n" +
                "b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "'\n" +
                " group by a.reservations_id) t) t) AS outpatientVolume,\n" +
                "(select count(1) from (select * from  ( select a.reservations_id from " + tableName2 + " b  left join \n" +
                "" + tableName1 + " a on a.customer_id = b.id left join pay_service_info c\n" +
                "on a.pay_service_id=c.id where b.customer_status='新客二次' and a.deal_status='已成交' and\n" +
                "b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "'\n" +
                " group by a.reservations_id) t) t)  AS volume,\n" +
                "(select sum(c.service_actual_pay) from " + tableName2 + " b  left join  " + tableName1 + " c on c.customer_id = b.id left join pay_service_info d on c.pay_service_id=d.id\n" +
                " where  b.customer_status='新客二次' and c.get_time>='" + searchInfo.getStartTime() + "' \n" +
                " and c.get_time<='" + searchInfo.getEndTime() + "'\n" +
                "  and c.deal_status='已成交' \n" +
                " ) AS departmentPerformance  from dual \n" +
                " union all\n" +
                "select '合计' as rirstClassification,\n" +
                "(select count(1) from (\n" +
                "select * from  ( \n" +
                "select a.reservations_id,b.customer_status from " + tableName2 + " b  left join \n" +
                "" + tableName1 + " a on a.customer_id = b.id left join pay_service_info c\n" +
                "on a.pay_service_id=c.id where \n" +
                "b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "'\n" +
                ") t where t.customer_status like '%新客%' group by t.reservations_id) t) AS outpatientVolume,\n" +
                " \n" +
                "(select count(1) from (select * from  ( select a.reservations_id,b.customer_status from " + tableName2 + " b  left join \n" +
                "" + tableName1 + " a on a.customer_id = b.id left join pay_service_info c\n" +
                "on a.pay_service_id=c.id where  a.deal_status='已成交' and\n" +
                "b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "'\n" +
                " ) t \n" +
                " where t.customer_status like '%新客%'  group by t.reservations_id ) t)  AS volume,\n" +
                "(select sum(c.service_actual_pay) from " + tableName2 + " b  left join  " + tableName1 + " c on c.customer_id = b.id left join pay_service_info d on c.pay_service_id=d.id\n" +
                " where  b.customer_status like '%新客%'  and c.get_time>='" + searchInfo.getStartTime() + "' \n" +
                " and c.get_time<='" + searchInfo.getEndTime() + "'\n" +
                "  and c.deal_status='已成交' \n" +
                " ) AS departmentPerformance  from dual \n" +
                " union all\n" +
                "select '合计' as rirstClassification,\n" +
                "(select count(1) from (select * from  ( select a.reservations_id from " + tableName2 + " b  left join \n" +
                "" + tableName1 + " a on a.customer_id = b.id left join pay_service_info c\n" +
                "on a.pay_service_id=c.id where b.customer_status='老客' and\n" +
                "b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "'\n" +
                " group by a.reservations_id) t) t) AS outpatientVolume,\n" +
                "(select count(1) from (select * from  ( select a.reservations_id from " + tableName2 + " b  left join \n" +
                "" + tableName1 + " a on a.customer_id = b.id left join pay_service_info c\n" +
                "on a.pay_service_id=c.id where b.customer_status='老客' and a.deal_status='已成交' and\n" +
                "b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "'\n" +
                " group by a.reservations_id) t) t)  AS volume,\n" +
                "(select sum(c.service_actual_pay) from " + tableName2 + " b  left join  " + tableName1 + " c on c.customer_id = b.id left join pay_service_info d on c.pay_service_id=d.id\n" +
                " where  b.customer_status='老客' and c.get_time>='" + searchInfo.getStartTime() + "' \n" +
                " and c.get_time<='" + searchInfo.getEndTime() + "'\n" +
                " and c.deal_status='已成交' \n" +
                " ) AS departmentPerformance  from dual \n" +
                " union all\n" +
                "select '合计' as rirstClassification,\n" +
                "(select count(1) from (\n" +
                "select * from  ( \n" +
                "select a.reservations_id,b.customer_status from " + tableName2 + " b  left join \n" +
                "" + tableName1 + " a on a.customer_id = b.id left join pay_service_info c\n" +
                "on a.pay_service_id=c.id where \n" +
                "b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "'\n" +
                ") t where t.customer_status!='复诊' and t.customer_status!='预约' group by t.reservations_id) t) AS outpatientVolume, \n" +
                "(select count(1) from (select * from  ( select a.reservations_id,b.customer_status from " + tableName2 + " b  left join \n" +
                "" + tableName1 + " a on a.customer_id = b.id left join pay_service_info c\n" +
                "on a.pay_service_id=c.id where  a.deal_status='已成交' and\n" +
                "b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "'\n" +
                " ) t \n" +
                " where t.customer_status!='复诊' and t.customer_status!='预约'  group by t.reservations_id) t)  AS volume,\n" +
                "(select sum(c.service_actual_pay) from " + tableName2 + " b  left join  " + tableName1 + " c on c.customer_id = b.id left join pay_service_info d on c.pay_service_id=d.id\n" +
                " where  b.customer_status!='复诊' and b.customer_status!='预约' and c.get_time>='" + searchInfo.getStartTime() + "' \n" +
                " and c.get_time<='" + searchInfo.getEndTime() + "'\n" +
                "  and c.deal_status='已成交' \n" +
                " ) AS departmentPerformance  from dual  ";
        return sql;
    }

}



